Imports DAL
Imports MyClasses
Imports System.Configuration
Imports System.Data.SqlClient

'@20071120
Public Class DFinancialYear

    Public Shared Function SaveYear(ByVal FinYearId As Integer) As Integer
        Dim dbConnection As New dbAccess
        Dim intResult As Integer = 0
        Try
            dbConnection.BeginTransaction()

            intResult = Updateyears(FinYearId, dbConnection)
            If intResult = 0 Then Exit Function

            dbConnection.CommitTransaction()
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DFinancialYear", "FinancialYear", "SaveYear")
            intResult = 0
        Finally
            If intResult = 0 Then
                dbConnection.RollBackTransaction()
                SaveYear = intResult
            Else : SaveYear = intResult
            End If
            dbConnection.closeSqlcon()
        End Try
    End Function

    Public Shared Function GetAllYears(ByVal FirmID As Integer) As DataTable
        Dim strsql As String = String.Empty
        Try
            strsql = " SELECT FinYrId, FIN_YEAR_LABEL " & _
                     " FROM TFINYEAR WHERE FirmId= " & FirmID & ""
            Return DbHelper.ExecuteDataTable(strsql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "FinancialYear", "DFinancialYear", "GetAllYears")
            Return Nothing
        End Try
    End Function

    Public Shared Function GetFinyears(ByVal FinYrId As Integer) As DataTable
        Dim strsql As String = String.Empty
        Try
            strsql = " SELECT Fin_start_dt, Fin_end_dt" & _
                     " FROM TFINYEAR " & _
                     " WHERE Finyrid = " & FinYrId & " " & _
                     " AND FirmId = " & ConfigurationManager.AppSettings("FirmID") & ""

            Return DbHelper.ExecuteDataTable(strsql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "FinancialYear", "DFinancialYear", "GetFinyears")
            Return Nothing
        End Try
    End Function

    Public Shared Function SetFinyear(ByVal FirmId As Integer)
        Dim strsql As String = String.Empty
        Try
            strsql = " SELECT Fin_start_dt, Fin_end_dt, FinYrId, Fin_Year_Label" & _
                     " FROM TFINYEAR " & _
                     " WHERE FirmId = " & FirmId & " AND Status = 'A' AND Working_Status = 'A'"

            Return DbHelper.ExecuteDataTable(strsql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "FinancialYear", "DFinancialYear", "SetFinyear")
            Return Nothing
        End Try
    End Function

    Public Shared Function GetWorkingFinYear(ByVal FirmId As Integer) As String
        Dim strsql As String = String.Empty
        Try
            'strsql = " SELECT Fin_Year_Label" & _
            '         " FROM TFINYEAR " & _
            '         " WHERE FirmId = " & FirmId & " AND Working_Status = 'A'"

            'Return DbHelper.ExecuteScalar(strsql)
            Return "2014-15"
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "FinancialYear", "DFinancialYear", "GetWorkingFinYear")
            Return Nothing
        End Try
    End Function

    Public Shared Function GetActiveFinyear(ByVal FirmId As Integer) As DataTable
        Dim strsql As String = String.Empty
        Try
            strsql = " SELECT Fin_start_dt, Fin_end_dt, FinYrId" & _
                     " FROM TFINYEAR " & _
                     " WHERE FirmId = " & FirmId & " AND " & _
                     " Status = 'A' "
            Return DbHelper.ExecuteDataTable(strsql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "FinancialYear", "DFinancialYear", "GetActiveFinyear")
            Return Nothing
        End Try
    End Function

    Public Shared Function GetWorkingFinyearID(ByVal FirmId As Integer) As Integer
        Dim strsql As String = String.Empty
        Try
            strsql = " SELECT FinYrId" & _
                     " FROM TFINYEAR " & _
                     " WHERE FirmId = " & FirmId & " AND " & _
                     " Working_Status = 'A' "
            Return DbHelper.ExecuteScalar(strsql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "FinancialYear", "DFinancialYear", "GetWorkingFinyearID")
            Return Nothing
        End Try
    End Function

    Public Shared Function Updateyears(ByVal FinYearId As Integer, ByRef dbConnection As dbAccess) As Integer
        Dim strsql As String = String.Empty
        Try
            strsql = " UPDATE TFINYEAR SET WORKING_STATUS = 'X'," & _
                     " LU = '" & CCommon.GetUserID & "', LD = getdate() " & _
                     " WHERE FirmId = " & ConfigurationManager.AppSettings("FirmID") & "" & _
                     " AND WORKING_STATUS = 'A' "

            dbConnection.ExecuteNonQuery(strsql)

            strsql = " UPDATE TFINYEAR SET WORKING_STATUS = 'A'," & _
                     " LU = '" & CCommon.GetUserID & "', LD = getdate() " & _
                     " WHERE FinYrId = " & FinYearId & " " & _
                     " AND FirmId = " & ConfigurationManager.AppSettings("FirmID") & ""

            Return dbConnection.ExecuteNonQuery(strsql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "FinancialYear", "DFinancialYear", "Updateyears")
            Return Nothing
        End Try
    End Function
End Class
